1 Imports System.Data.SqlClient
2 Imports Excel = Microsoft.Office.Interop.Excel
3
4 Public Class frmVoucherReport
5
6 Dim a As Decimal
7 Sub fillVoucherNo()
8 Try
9 Dim CN As New SqlConnection(cs)
10 CN.Open()
11 adp = New SqlDataAdapter()
12 adp.SelectCommand = New SqlCommand("SELECT distinct RTRIM(VoucherNo) FROM Voucher", CN)
13 ds = New DataSet("ds")
14 adp.Fill(ds)
15 dtable = ds.Tables(0)
16 cmbVoucherNo.Items.Clear()
17 For Each drow As DataRow In dtable.Rows
18 cmbVoucherNo.Items.Add(drow(0).ToString())
19 Next
20
21 Catch ex As Exception
22 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
23 End Try
24 End Sub
25
26 Private Sub frmLogs_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
27 fillVoucherNo()
28 End Sub
29 Sub Reset()
30 cmbVoucherNo.Text = ""
31 dtpDateFrom.Text = Today
32 dtpDateTo.Text = Today
33 fillVoucherNo()
34 End Sub
35 Private Sub btnReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnReset.Click
36 Reset()
37 End Sub
38
39
40 Private Sub btnClose_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
41 Me.Close()
42 End Sub
43
44 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
45
46 If cmbVoucherNo.Text = "" Then
47 MessageBox.Show("Please select voucher no.", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
48 cmbVoucherNo.Focus()
49 Exit Sub
50 End If
51 Try
52 Cursor = Cursors.WaitCursor
53 Timer1.Enabled = True
54 Dim rpt As New rptVoucher 'The report you created.
55 Dim myConnection As SqlConnection
56 Dim MyCommand, MyCommand1 As New SqlCommand()
57 Dim myDA, myDA1 As New SqlDataAdapter()
58 Dim myDS As New DataSet 'The DataSet you created.
59 myConnection = New SqlConnection(cs)
60 MyCommand.Connection = myConnection
61 MyCommand1.Connection = myConnection
62 MyCommand.CommandText = "SELECT Voucher.ID, Voucher.VoucherNo, Voucher.Date, Voucher.Name, Voucher.Details, Voucher.GrandTotal, Voucher_OtherDetails.VD_ID, Voucher_OtherDetails.VoucherID,Voucher_OtherDetails.Particulars, Voucher_OtherDetails.Amount, Voucher_OtherDetails.Note FROM Voucher INNER JOIN Voucher_OtherDetails ON Voucher.ID = Voucher_OtherDetails.VoucherID where VoucherNo='" & cmbVoucherNo.Text & "'"
63 MyCommand1.CommandText = "SELECT * from Company"
64 MyCommand.CommandType = CommandType.Text
65 MyCommand1.CommandType = CommandType.Text
66 myDA.SelectCommand = MyCommand
67 myDA1.SelectCommand = MyCommand1
68 myDA.Fill(myDS, "Voucher")
69 myDA.Fill(myDS, "Voucher_OtherDetails")
70 myDA1.Fill(myDS, "Company")
71 rpt.SetDataSource(myDS)
72 frmReport.CrystalReportViewer1.ReportSource = rpt
73 frmReport.ShowDialog()
74 Catch ex As Exception
75 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
76 End Try
77 End Sub
78
79 Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
80 Cursor = Cursors.Default
81 Timer1.Enabled = False
82 End Sub
83
84 Private Sub cmbVoucherNo_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles cmbVoucherNo.SelectedIndexChanged
85
86 End Sub
87
88 Private Sub btnGetData_Click(sender As System.Object, e As System.EventArgs) Handles btnGetData.Click
89 Try
90 Cursor = Cursors.WaitCursor
91 Timer1.Enabled = True
92 Dim rpt As New rptExpenses 'The report you created.
93 Dim myConnection As SqlConnection
94 Dim MyCommand As New SqlCommand()
95 Dim myDA As New SqlDataAdapter()
96 Dim myDS As New DataSet 'The DataSet you created.
97 myConnection = New SqlConnection(cs)
98 MyCommand.Connection = myConnection
99 MyCommand.CommandText = "SELECT Voucher.ID, Voucher.VoucherNo, Voucher.Date, Voucher.Name, Voucher.Details, Voucher.GrandTotal, Voucher_OtherDetails.VD_ID, Voucher_OtherDetails.VoucherID,Voucher_OtherDetails.Particulars, Voucher_OtherDetails.Amount, Voucher_OtherDetails.Note FROM Voucher INNER JOIN Voucher_OtherDetails ON Voucher.ID = Voucher_OtherDetails.VoucherID where date between @d1 and @d2 order by date"
100 MyCommand.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
101 MyCommand.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
102 MyCommand.CommandType = CommandType.Text
103 myDA.SelectCommand = MyCommand
104 myDA.Fill(myDS, "Voucher")
105 myDA.Fill(myDS, "Voucher_OtherDetails")
106 con = New SqlConnection(cs)
107 con.Open()
108 Dim ct As String = "select ISNULL(sum(GrandTotal),0) from Voucher where Date between @d1 and @d2"
109 cmd = New SqlCommand(ct)
110 cmd.Parameters.Add("@d1", SqlDbType.DateTime, 30, "Date").Value = dtpDateFrom.Value.Date
111 cmd.Parameters.Add("@d2", SqlDbType.DateTime, 30, "Date").Value = dtpDateTo.Value.Date
112 cmd.Connection = con
113 rdr = cmd.ExecuteReader()
114 While rdr.Read()
115 a = rdr.GetValue(0)
116 End While
117 rpt.SetDataSource(myDS)
118 rpt.SetParameterValue("p1", dtpDateFrom.Value.Date)
119 rpt.SetParameterValue("p2", dtpDateTo.Value.Date)
120 rpt.SetParameterValue("p3", a)
121 rpt.SetParameterValue("p4", Today)
122 frmReport.CrystalReportViewer1.ReportSource = rpt
123 frmReport.ShowDialog()
124 Catch ex As Exception
125 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
126 End Try
127 End Sub
128 End Class
